# -*- encoding:utf-8 -*-
import json
from flask import Blueprint, g
from psycopg2 import connect
from psycopg2.extras import RealDictCursor
from lib.table import table
from config import config
from lib.record import recodelog
from lib import auth
from lib.validform import V, ValidateForm
app = Blueprint(__name__ + "_app", __name__)


# 连接/关闭 数据库
@app.before_request
def setupdb():
    g.conn = connect(**config.DatabaseConfig.siem)
    g.cursor = g.conn.cursor(cursor_factory=RealDictCursor)


@app.teardown_request
def unsetdb(exception):
    if g.cursor:
        g.cursor.close()
        g.cursor = None
    if g.conn:
        g.conn.close()
        g.conn = None


# 获取用户组角色ID

@app.route('/api/user/roleid', methods=['POST'])
def role_id():
    try:
        form = ValidateForm(user_group_id=['用户组ID', V.required()])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        g.cursor.execute(
            """ select string_agg(distinct srf.group_id,',') as group_id,
                             string_agg(distinct sr.role_name,',') as role_name
                             from sys_usergroup_role as sur
                             left join sys_role as sr on sur.role_id = sr.role_id
                             left join sys_role_function as srf on srf.role_id = sr.role_id
                             where user_group_id = %(user_group_id)s""",
            (data))
        row = g.cursor.fetchall()
        return json.dumps({'status': 'success', 'msg': row})
    except Exception, e:
        return json.dumps({
            "status": "except",
            'msg': '获取信息失败'
        })


# 获取用户组角色名
@app.route('/api/user/role', methods=['POST'])
def role_name():
    try:
        g.cursor.execute(
            """ select sr.role_id,sr.role_name,string_agg(sfg.cn_name,',') from sys_role as sr
                             left join sys_role_function as srf on sr.role_id = srf.role_id
                             left join sys_function_group as sfg on sfg.group_id = srf.group_id  where sr.state = '1' and sfg.state = '1'
                             group by sr.role_name,sr.role_id""")
        row = g.cursor.fetchall()
        return json.dumps({'status': 'success', 'msg': row})
    except Exception, e:
        return json.dumps({
            "status": "except",
            'msg': '获取用户组角色名失败'
        })


# 用户组选项获取
@app.route('/api/user/options', methods=['GET'])
def options():
    try:
        g.cursor.execute(
            """select  user_group_name,user_group_id from  sys_user_group where state = '1' """
        )
        row = g.cursor.fetchall()
        return json.dumps({"status": "success", 'msg': row})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '获取用户组选项失败'})


# 获取用户组信息
@app.route('/api/user/usersgroup', methods=['POST'])
@auth.permission("user", "readOnly")
def users_group():
    try:
        tb = table()
        g.cursor.execute(
            """select  count(a.user_group_name) as num
                from
                sys_user_group as a %s""" % (tb.where(
                ['a.user_group_name'], "state = '1'")))
        total = g.cursor.fetchone()['num']
        g.cursor.execute(
            """ select  a.user_group_name as auser_group_name,string_agg(distinct sr.role_name,','),count(distinct sp.person_id),
                a.r_time,a.user_group_id,string_agg(distinct sr.role_id,',') as role_id,a.r_person
                from
                (select user_group_id,user_group_name ,d2t(r_time) as r_time,r_person from sys_user_group where state = '1') as a
                left join sys_usergroup_role as sur on a.user_group_id = sur.user_group_id
                left join (select role_id,role_name from sys_role where state='1') sr on sr.role_id = sur.role_id
                left join sys_login_group sl on sur.user_group_id=sl.user_group_id
                left join (select login_id,person_id from sys_login where state='1') sg on sg.login_id = sl.login_id
                left join (select person_id from sys_person where state = '1') sp  on sg.person_id = sp.person_id %s
                group by a.user_group_name,a.r_time,a.user_group_id,a.r_person
                %s %s %s""" % (tb.where(
                ['a.user_group_name']), tb.orderBy(), tb.offset(), tb.limit()))
        row = g.cursor.fetchall()
        return json.dumps({
            "status": "success",
            "msg": {
                "current": tb.current(),
                "total": total,
                "dataSource": row
            }
        })
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '获取用户组信息失败'})


# 添加用户组
@app.route('/api/user/addgroup', methods=['POST'])
@auth.permission("user")
def add_group(_currUser):
    try:
        form = ValidateForm(
            user_group_name=['用户组', V.required()],
            role_name=['角色名', V.required()])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        g.cursor.execute("SELECT public.get_location_seq('sys_user_group')")
        user_group_id = g.cursor.fetchone()
        g.cursor.execute(
            """insert into sys_user_group(user_group_name,user_group_id,state,r_person,data_source)
                            values('%s','%s','1','%s','1')""" % (data['user_group_name'], user_group_id['get_location_seq'], _currUser['user']['person_id']))
        role_name = data['role_name'].replace('\"', '\'')
        g.cursor.execute(
            """insert into sys_usergroup_role(user_group_id,role_id)
                            select user_group_id,role_id from sys_user_group,sys_role
                            where user_group_id='%s' and role_name in(%s)""" % (user_group_id['get_location_seq'], role_name))
        g.conn.commit()
        rem = {
            "用户组": "%s" % data['user_group_name'],
            "角色名": "%s" % data['role_name']
        }
        auth.logsync(_currUser, {
            "function": "用户管理",
            "type": "新增",
            "remark": "新增用户组信息:" + recodelog(rem)
        })
        return json.dumps({"status": 'success', 'msg': '新增成功'})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '新增用户组失败'})


# 修改用户组
@app.route('/api/user/updategroup', methods=['POST'])
@auth.permission("user")
def update_group(_currUser):
    try:
        form = ValidateForm(
            user_group_name=['用户组', V.required()],
            role_name=['角色名', V.required()],
            user_group_id=['用户组ID', V.required()],
            r_person=['记录人', V.required()]
        )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        g.cursor.execute(
            """delete from sys_usergroup_role where user_group_id = %(user_group_id)s""",
            (data))
        g.cursor.execute(
            """update sys_user_group set user_group_name = %(user_group_name)s,r_person=%(r_person)s where user_group_id = %(user_group_id)s""",
            (data))
        role_name = data['role_name'].replace('\"', '\'')
        g.cursor.execute(
            """insert into sys_usergroup_role(user_group_id,role_id)
                            select user_group_id,role_id from sys_user_group,sys_role
                            where user_group_id='%s' and role_name in(%s)""" % (data['user_group_id'], role_name))
        g.conn.commit()
        rem = {
            "用户组ID": "%s" % data['user_group_id'],
            "用户组名称": "%s" % data['user_group_name'],
            "角色名": "%s" % data['role_name'],
            "记录人": "%s" % data['r_person']
        }
        auth.logsync(_currUser, {
            "function": "用户管理",
            "type": "修改",
            "remark": "修改用户组信息:" + recodelog(rem)
        })
        return json.dumps({"status": 'success', 'msg': '修改成功'})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '修改用户组失败'})


# 删除用户组
@app.route('/api/user/delgroup', methods=['POST'])
@auth.permission("user")
def del_group(_currUser):
    try:
        form = ValidateForm(user_group_id=['用户组', V.required()], user_group_name=['用户组名', V.required()], r_person=['记录人', V.required()])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        g.cursor.execute(
            """update  sys_user_group set state = '0',r_person=%(r_person)s where user_group_id = %(user_group_id)s """,
            (data))
        g.conn.commit()
        rem = {
            "用户组ID": "%s" % data['user_group_id'],
            "用户组": "%s" % data['user_group_name'],
            "记录人": "%s" % data['r_person']
        }
        auth.logsync(_currUser, {
            "function": "用户管理",
            "type": "删除",
            "remark": "删除用户组信息:" + recodelog(rem)
        })
        return json.dumps({"status": 'success', 'msg': '删除成功'})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '删除用户组失败'})


# 用户组详情
@app.route('/api/user/groupdetailed', methods=['POST'])
@auth.permission("user", "readOnly")
def group_detailed():
    try:
        form = ValidateForm(user_group_id=['用户组ID', V.required()])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        g.cursor.execute(
            """ select sug.user_group_name,sug.user_group_id,string_agg(distinct sr.role_name,',')as role_name
                from
                (select user_group_id,user_group_name from sys_user_group where state = '1') as sug
                left join sys_usergroup_role as sur on sug.user_group_id = sur.user_group_id
                left join (select role_id,role_name from sys_role where state='1') sr on sr.role_id = sur.role_id
                left join sys_role_function as srf on sr.role_id = srf.role_id
                left join (select group_id from sys_function_group where state = '1') as sfg on sfg.group_id = srf.group_id where sug.user_group_id = %(user_group_id)s
                group by sug.user_group_name,sug.user_group_id""", (data))
        row = g.cursor.fetchall()
        return json.dumps({"status": 'success', 'msg': row})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '获取用户组详情失败'})


# 用户组右侧详情
@app.route('/api/user/rightdetailed', methods=['POST'])
@auth.permission("user", "readOnly")
def right_detailed():
    try:
        form = ValidateForm(user_group_id=['用户组ID', V.required()])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        g.cursor.execute(
            """ select sug.user_group_name,sr.role_name,sug.user_group_id,string_agg(sfg.cn_name,',')as role
                from
                (select user_group_id,user_group_name from sys_user_group where state = '1') as sug
                left join sys_usergroup_role as sur on sug.user_group_id = sur.user_group_id
                left join sys_role sr on sr.role_id = sur.role_id
                left join sys_role_function as srf on sr.role_id = srf.role_id
                left join (select cn_name,group_id from sys_function_group where state = '1')as sfg on sfg.group_id = srf.group_id where sur.user_group_id = %(user_group_id)s and sr.state = '1'
                group by sug.user_group_name,sug.user_group_id,sr.role_name""",
            (data))
        row = g.cursor.fetchall()
        return json.dumps({"status": 'success', 'msg': row})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '获取信息失败'})


# 校验用户组名称
@app.route('/api/group/name', methods=['POST'])
@auth.permission("user", "readOnly")
def group_jiance():
    try:
        form = ValidateForm(user_group_name=['用户组名', V.required()],user_group_id=['用户组ID', V.optional()], )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        g.cursor.execute(
            """select user_group_name from sys_user_group where user_group_name = %(user_group_name)s and state='1' and user_group_id != %(user_group_id)s""",
            (data))
        user_name = g.cursor.fetchone()
        if user_name:
            return json.dumps({"status": "error", "msg": "用户组名已存在"})
        else:
            return json.dumps({"status": "success", "msg": "用户组可以创建"})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': "查询失败"})


# 同步信息数量接口
@app.route('/api/group/snyccount', methods=['POST'])
@auth.permission("user", "readOnly")
def snyccount(_currUser):
    try:
        g.cursor.execute(
            """select count(table_id) from h_synchroniz_info where person_id = '%s' and state ='1' and synchroniz_table = 'sys_user_group' """ % (_currUser['user']['person_id']))
        count = g.cursor.fetchone()
        return json.dumps({"status": "success", "msg": count})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '获取同步信息数量失败'})


# 已查看同步信息
@app.route('/api/group/delsnyccount', methods=['POST'])
@auth.permission("user", "readOnly")
def delsnyccount(_currUser):
    try:
        g.cursor.execute(
            """update h_synchroniz_info set state = '0' where person_id = '%s' and synchroniz_table = 'sys_user_group' """ % (_currUser['user']['person_id']))
        g.conn.commit()
        return json.dumps({"status": "success", "msg": '同步信息删除成功'})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '同步信息删除失败'})


# 同步信息
@app.route('/api/group/snycmessage', methods=['POST'])
@auth.permission("user", "readOnly")
def snycmessage(_currUser):
    try:
        tb = table()
        _where = """a.synchroniz_table = 'sys_user_group' and a.person_id = '%s'
                and a.state = '1' and a.operating = '%s'""" % (_currUser['user']['person_id'], tb.dict['type'])

        g.cursor.execute(
            """SELECT d2t(z.r_time) as zr_time,z.user_group_id as auser_group_id,z.user_group_name_change as auser_group_name,z.role_group_change as string_agg,
                        c.r_person,z.user_group_count_change as count
                from h_synchroniz_info a
                left join h_usergroup_synchroniz_info z on a.r_time = z.r_time
                left join sys_user_group c on z.user_group_id = c.user_group_id
                %s %s""" % (tb.where(['z.user_group_id', 'z.user_group_name_change'], _where), tb.orderBy()))
        row = g.cursor.fetchall()
        return json.dumps({
            "status": "success",
            "msg": row
        })
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '同步信息获取失败'})


# 同步历史信息
@app.route('/api/group/snyclog', methods=['POST'])
@auth.permission("user", "readOnly")
def snyclog(_currUser):
    try:
        tb = table()
        _where = """a.synchroniz_table = 'sys_user_group' and a.person_id = '%s'
                and a.state = '0' and a.operating = '%s'""" % (_currUser['user']['person_id'], tb.dict['type'])
        g.cursor.execute(
            """SELECT count(1) as num
            from(SELECT d2t(z.r_time) as zr_time,z.user_group_id as user_group_id,z.user_group_name_change as auser_group_name,z.role_group_change as string_agg,c.r_person
                from h_synchroniz_info a
                left join h_usergroup_synchroniz_info z on a.r_time = z.r_time
                left join sys_user_group c on z.user_group_id = c.user_group_id
                %s) a""" % (tb.where(['z.user_group_name_change'], _where)))
        total = g.cursor.fetchone()['num']
        g.cursor.execute(
            """SELECT d2t(z.r_time) as zr_time,z.user_group_id as user_group_id,z.user_group_name_change as auser_group_name,z.role_group_change as string_agg,c.r_person,z.user_group_count_change as count
                from h_synchroniz_info a
                left join h_usergroup_synchroniz_info z on a.r_time = z.r_time
                left join sys_user_group c on z.user_group_id = c.user_group_id
                %s %s %s %s
               """ % (tb.where(['z.user_group_name_change'], _where), tb.orderBy(), tb.offset(), tb.limit()))
        return json.dumps({
            "status": "success",
            "data": {
                "current": tb.current(),
                "total": total,
                "dataSource": g.cursor.fetchall()
            }
        })
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '同步历史信息获取失败','s':str(e)})
